package isae.lib;

import java.util.Date;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author admin
 */
public class InitialLists {

    Connection connection;

    public InitialLists() {
    }

    public List<Centre> avoirListeCentre() throws ClassNotFoundException, SQLException {

        Statement stmt;
        String requete = "select IdCentre,NomCentre from Centre order by IdCentre";
        ResultSet records;
        List<Centre> l = new LinkedList<Centre>();

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Centre(records.getInt(1), records.getString(2)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        //return r;
        return l;
    }

    public List<MatiereT> avoirListeMatiereT(int annee, int ouv, int nocentre) throws ClassNotFoundException {

        Statement stmt;
        String requete = "select t1.NomatiereT,intituleT,DateDebutE,DateFinE,DateExamen ";
        requete += "from MatiereTest as t1,OuvrirTest as t2 ";
        requete += "where t1.NomatiereT=t2.NomatiereT and annee=" + annee + " and NoOuvertureT=" + ouv;
        requete += " and IdCentre=" + nocentre;

        ResultSet records;
        List<MatiereT> l = new LinkedList<MatiereT>();

        try {

            connection = getJdbcDetail().getConnection();

            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                boolean enable = false;
                Date today = new Date();
                Date dd = (Date) records.getDate(3);
                Date df = (Date) records.getDate(4);

                if ((dd.compareTo(today) <= 0) && (df.compareTo(today) >= 0)) {
                    enable = true;
                }

                l.add(new MatiereT(records.getInt(1), records.getString(2), enable, records.getDate(3), records.getDate(4), records.getTimestamp(5)));

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }

        return l;
    }

    public List<Annee> avoirListeAnnees() throws ClassNotFoundException, SQLException {

        Statement stmt;
        String requete = "select annee from ListeAnnees where annee>='2000' order by annee desc ";
        ResultSet records;
        List<Annee> l = new LinkedList<Annee>();

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Annee(records.getInt(1)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        //return r;
        return l;
    }

    public List<Annee> avoirAnnees() throws ClassNotFoundException, SQLException {

        Statement stmt;
        String requete = "select annee from ListeAnnees order by annee desc ";
        ResultSet records;
        List<Annee> l = new LinkedList<Annee>();

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Annee(records.getInt(1)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<Periode> avoirPeriode() throws ClassNotFoundException {

        Statement stmt;
        String requete = "select NoPeriode,DateDebutP,DateFinP,LabelPeriode,DateEcheance,DatePenalite ";
        requete += "from PeriodeInscription ";
        requete += "order by DateDebutP DESC";

        ResultSet records;
        List<Periode> l = new LinkedList<Periode>();

        try {

            connection = getJdbcDetail().getConnection();

            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {

                l.add(new Periode(records.getInt(1), records.getDate(2), records.getDate(3), records.getString(4), records.getDate(5), records.getDate(6)));

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }

        return l;
    }

    public List<DiplomeType> avoirListeDiplomeType() throws ClassNotFoundException {

        Statement stmt;
        String requete = "select NoDiplome, NomDiplome, NiveauD, NbAnnee from Diplome_type order by NiveauD";
        ResultSet records;
        List<DiplomeType> l = new LinkedList<DiplomeType>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new DiplomeType(records.getInt(1), records.getString(2), records.getString(3), records.getInt(4)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<Specialite> avoirListeSpec() throws ClassNotFoundException {

        Statement stmt;
        String requete = "select spec,specialite from Specialite where anfin is null order by specialite,spec";
        ResultSet records;
        List<Specialite> l = new LinkedList<Specialite>();
        //String r = "vide";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Specialite(records.getInt(1), records.getString(2)));
            //System.out.println(records.getInt(1));
            //System.out.println(records.getString(2));
            //r = records.getString(2);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        //return r;
        return l;
    }

    public List<Specialite> avoirListeSpecTous() throws ClassNotFoundException {

        Statement stmt;
        String requete = "select spec,specialite from Specialite order by specialite,spec";
        ResultSet records;
        List<Specialite> l = new LinkedList<Specialite>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Specialite(records.getInt(1), records.getString(2)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<User> avoirUtilisateur(String user, String pass) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select UserId, username, password, fonction, no, fermer from Users where password=" + "\"" + pass + "\"" + "and username=" + "\"" + user + "\"";
        ResultSet records;
        List<User> l = new LinkedList<User>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new User(records.getInt(1), records.getString(2), records.getString(3), records.getString(4), records.getInt(5), records.getInt(6)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<Specialite> avoirListeSpec2(int Nochef) throws ClassNotFoundException {

        Statement stmt;
        String requete = "select spec,specialite from Specialite where Nochef=" + "\"" + Nochef + "\"" + "and anfin is null order by specialite";
        ResultSet records;
        List<Specialite> l = new LinkedList<Specialite>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Specialite(records.getInt(1), records.getString(2)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<DiplomePosseder> avoirDiplomePosseder(int Noetud) throws ClassNotFoundException {
        Statement stmt;
        List<DiplomePosseder> l = new LinkedList<DiplomePosseder>();
        String requete = "select NoEtud,Diplome_posseder.NoDiplome,NomDiplome,specialitedip,NomEtab,DateObtDH,ProofDH from Diplome_posseder,Diplome_type where ";
        requete += "NoEtud=" + Noetud + " and Diplome_posseder.NoDiplome=Diplome_type.Nodiplome";

        ResultSet records;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new DiplomePosseder(records.getInt(1), records.getInt(2), records.getString(3), records.getString(4), records.getString(5), records.getDate(6), records.getBoolean(7)));

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<DroitAcces> avoirDroitAcces(int id) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select UserId, typedroit from DroitAcces where UserId=" + id;
        ResultSet records;
        List<DroitAcces> l = new LinkedList<DroitAcces>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new DroitAcces(records.getInt(1), records.getString(2)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<FicheInfo> avoirFicheInfo(int nofiche) throws ClassNotFoundException {

        Statement stmt;
        String requete = "select NoFiche,code_etudier,dates,payee,payerp,dispense,payeemoitie,deuxvers,retour,DateEcheance ";
        requete += "from Fiche where NoFiche=" + nofiche;


        ResultSet records;
        List<FicheInfo> l = new LinkedList<FicheInfo>();

        try {

            connection = getJdbcDetail().getConnection();

            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {

                l.add(new FicheInfo(records.getInt(1), records.getInt(2), records.getDate(3), records.getBoolean(4), records.getBoolean(5), records.getBoolean(6), records.getBoolean(7), records.getBoolean(8), records.getInt(9), records.getDate(10)));

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }

        return l;
    }

    public List<Annee> avoirAnnees2010() throws ClassNotFoundException, SQLException {

        Statement stmt;
        String requete = "select annee from ListeAnnees where annee>='2010' order by annee desc ";
        ResultSet records;
        List<Annee> l = new LinkedList<Annee>();

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Annee(records.getInt(1)));
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<Chef> avoirListeChef() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NoChef,Nomch,Prenomch from Chef order by NoChef";
        ResultSet records;

        List<Chef> l = new LinkedList<Chef>();
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                l.add(new Chef(records.getInt(1), records.getString(2), records.getString(3)));
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialLists.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    private DataSource getJdbcDetail() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/Detail");
    }
}
